package jwanalytics.dbloader;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;

import org.apache.log4j.Logger;

import jwanalytics.utils.date.BaseCalendar;

/** 
 * This is an interesting class to populate the date_dimension table, including creating
 * a score which is higher for dates closer to weekends and holidays (useful for hospitality 
 * and other holiday seasonal industries).
 * 
 * Notice that it will be called automatically by the RecreateDatabase class.
 * 
 * This method is pre-optimized for batch insert performance.
 * 
 * @see RecreateDatabase
 * @author CestaA
 */
public class PopulateDateDimension {
	
	/** Logger for all pages. */
	public static Logger logger = Logger.getLogger("jwanalytics");
	
	/**
	 * Main method will start execution immediately without prompting command line caller.
	 * @param args Not used.
	 */
	public static void main(String[] args) throws Exception {
		//Prepare database connection.
		Connection con  = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			Props.init(Props.PROPS_FILE, false);
			int currentDay = 1; //1st Jan 2005 14:00 (to avoid problems with daylight savings +-1 hour when starting at 00:00
			int maxYear = Integer.parseInt(Props.get(Props.PROPS_FILE, "end_analytics_year"));
			float holidayForwardDensityIndex = 0;
			//1st of January 2005.  Start of simulation which can go up to max 2027.
			String startAnalyticsStr = Props.get(Props.PROPS_FILE, "start_analytics_date");
			SimpleDateFormat sdf = new java.text.SimpleDateFormat("yy-mm-dd");
			Date startAnalyticsDate = sdf.parse(startAnalyticsStr);
			GregorianCalendar vCurrDate = new GregorianCalendar();
			vCurrDate.setTime(startAnalyticsDate);
			GregorianCalendar vTmpCal = new GregorianCalendar();
			boolean isHoliday = false;
			boolean isWeekend = false;
			//
			boolean couldCreate = true;
			try {
				//Most of the time this table will fail to drop due to referencial integrity. But we at least try it.
				DB.dropStatement(Props.get(Props.PROPS_FILE, "date_dimension.drop_table_ddl"));
				logger.debug("Table dropped:date_dimension");
			} catch(Exception e) {
				logger.debug("Table could not be dropped:date_dimension. Most common reasons: does no exist, or referencial integrity:"+e);				
			}
			//Create table without pk for fast loading.
			try {
				DB.executeStatement(Props.get(Props.PROPS_FILE, "date_dimension.create_table_ddl"));
				logger.debug("Table created:date_dimension");
			} catch(Exception e) {
				couldCreate = false;
				logger.debug("Table could not be created:date_dimension, probably because it already existed:"+e);
			}
			//In some cases it was never dropped, never created, so try to delete rows from it them. Which may also fail due to referencial integrity.
			try {
				DB.executeStatement("DELETE FROM DATE_DIMENSION");
				logger.debug("Table rows deleted:date_dimension");
			} catch(Exception e) {
				logger.debug("Table rows could not be deleted, probably due to referencial integrity:"+e);
			}			
			//
			con = DB.getConnection();			
			st = con.createStatement();
			int[] updateCounts; //for counting the multiple updates done on populating the dimensions.
			StringBuffer sb;
			//@TODO: externalise these "constants" below 1.20f,100f,45f to the Ontology.
			float ATTENUATING_FACTOR = 1.20f;
			float WEEKEND_DAY_POINTS = 45f;
			float HOLIDAY_POINTS = 50; //only when holiday does not fall on a Saturday or Sunday.
			con.setAutoCommit(false);
			HashMap years = new HashMap(); //just to keep track of which years are being created.
			while (vCurrDate.get(Calendar.YEAR) != maxYear) {
				//print progress through years.
				if (logger.isDebugEnabled()){
					String year = ""+vCurrDate.get(Calendar.YEAR);
					if (!years.containsKey(year)) {
						logger.debug("Creating dates for year:"+year+", max year:"+maxYear);
						years.put(year, year);
					}
				}
				//Dilute holidayForwardDensityIndex by dividing it by 120%.  Effects still visible more than 30 days later.
				//But will stabilize tending to a locale index for holidays which is always above zero and varies little year after year.
				holidayForwardDensityIndex = holidayForwardDensityIndex / ATTENUATING_FACTOR; //Take 20% out of holiday index.
				//@TODO: externalise this constant below 1.20f to the Ontology.
				isHoliday = BaseCalendar.isHoliday(vCurrDate.getTime(), null, null, "NL");
				isWeekend = BaseCalendar.isWeekend(vCurrDate.getTime());
				//Add to holidayForwardDensityIndex.
				if (isHoliday) {
					holidayForwardDensityIndex += HOLIDAY_POINTS;
				} else if (isWeekend) {
					holidayForwardDensityIndex += WEEKEND_DAY_POINTS;
				}
				String dateStr = vCurrDate.get(Calendar.YEAR)+"-"+(vCurrDate.get(Calendar.MONTH)+1)+"-"+vCurrDate.get(Calendar.DAY_OF_MONTH); 
				String holidayStr = (isHoliday? "Holiday":"Non holiday");
				String weekendStr = (isWeekend? "Weekend":"Non weekend");
				vTmpCal.setTime(BaseCalendar.addDays(vCurrDate.getTime(), 1));
				vCurrDate = vTmpCal;
				//Generate insert into database and then process it backwards adding to the holidayForwardDensityIndex.
				sb = new StringBuffer("insert into date_dimension (date_key, date, weekend_status, holiday_status, holiday_density_index) values(");
				sb.append(currentDay).append(",");
				sb.append("'").append(dateStr).append("',");
				sb.append("'").append(weekendStr).append("',");
				sb.append("'").append(holidayStr).append("',");
				sb.append(holidayForwardDensityIndex);
				sb.append(")");
				st.addBatch(sb.toString());
				//logger.debug("Executing:"+sb.toString());
				currentDay++;
				//Every 2 years we execute the batches to avoid having too many batches accumulated.
				if (vCurrDate.get(Calendar.YEAR) % 2 == 0) {
					updateCounts = st.executeBatch();
					st.clearBatch();
					con.commit();
				}
			} //WHILE end.
			updateCounts = st.executeBatch();		
			con.commit();
			//Now add primary key to make select and update more efficient.
			DB.close(con, st, null);
			if (couldCreate) { //otherwise these should exist already.
				logger.debug("Recreating indexes.");
				DB.executeStatement(Props.get(Props.PROPS_FILE, "date_dimension.alter_table_add_primary_key_ddl"));
				DB.executeStatement(Props.get(Props.PROPS_FILE, "date_dimension.create_index_holiday_status_ddl"));			
				DB.executeStatement(Props.get(Props.PROPS_FILE, "date_dimension.create_index_weekend_status_ddl"));
			}
			//
			//Now open table, iterate in descending order from id updating the index for holidays in backwards manner.
			con = DB.getConnection();
			con.setAutoCommit(true);
			//This SQL is specific for index creation and will not be factored out. It is also common to all databases (Standard SQL).
			st = con.prepareStatement("SELECT date_key, weekend_status, holiday_status, holiday_density_index from date_dimension order by date_key desc");
			rs = ((PreparedStatement)st).executeQuery();
			float holidayBackwardDensityIndex = 0;
			st = con.createStatement();
			while (rs.next()) {
				holidayBackwardDensityIndex = holidayBackwardDensityIndex / ATTENUATING_FACTOR; //Take 20% out of holiday index.
				isHoliday = !rs.getString("holiday_status").startsWith("Non");
				isWeekend = !rs.getString("weekend_status").startsWith("Non");			
				//Add to holidayBackwardDensityIndex.
				if (isHoliday) {
					holidayBackwardDensityIndex+=HOLIDAY_POINTS;
				} else if (isWeekend) {
					holidayBackwardDensityIndex+=WEEKEND_DAY_POINTS;
				}
				//This SQL is specific for index creation and will not be factored out. It is also common to all databases (standard SQL).
				sb = new StringBuffer("update date_dimension set holiday_density_index = holiday_density_index + ");
				sb.append(holidayBackwardDensityIndex).append(" where date_key = ").append(rs.getInt("date_key"));
				st.addBatch(sb.toString());
				if (isWeekend) { //update in batches of 5 and 1 (saturday goes in batches of 1).  Better than keeping a counter.
					updateCounts = st.executeBatch();
					st.clearBatch();				
				}
			}
			updateCounts = st.executeBatch();
			con.commit();
		} catch(Exception e) {
			logger.error("WAERROR0010:"+e);
			if (logger.isDebugEnabled()) {
				e.printStackTrace();
			}
		} finally {
			DB.close(con, st, rs);
		}
	}

}
